package excel.countryMedicine;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import po.MyObject;
import util.AbstractExl;
import util.Conts;

public class GSP extends AbstractExl {
		private static final Logger LOG = Logger.getLogger(GSP.class);
		private static String fileName = "GSP认证";
		private static int rowNum = Conts.PAGE_GSP;
		private static String filePath = Conts.HTM_ROOT_PATH+fileName+"/";
		private static String outputFolder = Conts.EXL_ROOT_PATH;
		private static String regex = 
//				"<tr>.*?"
//				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">省市</td>.*?"
//				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
//				+"<tr>.*?"
//				+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">证书编号</td>.*?"
//				+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
//				+"<tr>.*?"
//				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">企业名称</td>.*?"
//				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
//				+"<tr>.*?"
//				+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">地址</td>.*?"
//				+"<td bgcolor=\"#ffffff\" width=83%>(.*?)<br></td>.*?</tr>.*?"
//				+"<tr>.*?"
//				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">认证范围</td>.*?"
//				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
//				+"<tr>.*?"
//				+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">发证时间</td>.*?"
//				+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
//				+"<tr>.*?"
//				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">有效期截止日</td>.*?"
//				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?id is 【(.*?)】";
				
				"<tr>.*?"
				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">省市</td>.*?"
				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
				+"<tr>.*?"
				+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">证书编号</td>.*?"
				+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
				+"<tr>.*?"
				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">企业名称</td>.*?"
				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
				+"<tr>.*?"
				+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">地址</td>.*?"
				+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
				+"<tr>.*?"
				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">认证范围</td>.*?"
				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?"
				+"<tr>.*?"
				+"<td bgcolor=\"#ffffff\" style=\"text-align:right\" width=17% nowrap=\"true\">发证时间</td>.*?"
				+"<td bgcolor=\"#ffffff\" width=83%>(.*?)</td>.*?</tr>.*?"
				+"<tr>.*?"
				+"<td bgcolor=\"#eaeaea\" style=\"text-align:right\" width=17% nowrap=\"true\">有效期截止日</td>.*?"
				+"<td bgcolor=\"#eaeaea\" width=83%>(.*?)</td>.*?</tr>.*?id is 【(.*?)】";

		private void writeExl(List<MyObject> list) {
			File folder = new File(outputFolder);
			if (!folder.exists()) folder.mkdirs();
			FileOutputStream fOut = null;
			try {
				Workbook workbook = new SXSSFWorkbook(2000);
				Sheet sheet = workbook.createSheet("Sheet1");
				Row row = null;
				fOut = new FileOutputStream(outputFolder+fileName+".xlsx");
					for (int i = 0; i < list.size(); i++) {
						row = sheet.createRow(i);
						row.createCell((short)0).setCellValue(list.get(i).getObj1().trim());
						row.createCell((short)1).setCellValue(list.get(i).getObj2().trim());
						row.createCell((short)2).setCellValue(list.get(i).getObj3().trim());
						row.createCell((short)3).setCellValue(list.get(i).getObj4().trim());
						row.createCell((short)4).setCellValue(list.get(i).getObj5().trim());
						row.createCell((short)5).setCellValue(list.get(i).getObj6().trim());
						row.createCell((short)6).setCellValue(list.get(i).getObj7().trim());
						row.createCell((short)7).setCellValue(list.get(i).getObj8());
//						LOG.info("It had "+i+" rows");
					}
				workbook.write(fOut);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (null != fOut) {
					try {
						fOut.close();
						LOG.info(fileName +" success export excel ! And the excel had " + list.size() + " rows.");
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}
		}
		
		private void excute() {
			try {
				List<MyObject> list = new ArrayList<MyObject>();
				StringBuffer content = null;
				StringBuilder date = new StringBuilder();
				MyObject o = new MyObject();
				o.setObj1("REGION_NAME");
				o.setObj2("GSP_CODE");
				o.setObj3("FACTORY_NAME");
				o.setObj4("ADDRESS");
				o.setObj5("GSPC_LIMIT");
				o.setObj6("APPROVE_DATE");
				o.setObj7("VALID_DATE");
				o.setObj8("GSPC_ID");
				list.add(o);
				for (int i = 1; i <= rowNum; i++) {
					content = readHtm(filePath+i+".htm");
					Pattern pattern = Pattern.compile(regex);
					Matcher matcher = pattern.matcher(content);
					while (matcher.find()) {
						o = new MyObject();
						o.setObj1(matcher.group(1).replace("<br>", "").trim());
						o.setObj2(matcher.group(2).trim().replace("<br>", "")	);
						o.setObj3(matcher.group(3).replace("?", "").trim().replaceAll("<br>", ""));
						if(matcher.group(4).trim().equals("2006.12.11")) {
							o.setObj4("");
						} else {
							o.setObj4(matcher.group(4).replace("<br>", "").replace("?", "").replace("？", "").trim());
						}
						o.setObj5(matcher.group(5).replace("　", "").replace(" ", "")
								.replace("?", "").replace("。", "").replace("<br>", "").trim());
						date.append(matcher.group(6).trim().replace("/", "-").replace("..", "-")
								.replace(".", "-").replace(" ", "").replace("、", "-").replace("<br>", "")
								.replace("?", "").replace("．", "-").replace("200-08", "2000-08")
								.replace("认证时间", "").replace("发证日期", "")
								.replace("200411-13", "2004-11-13").replace("2009-13-31", "2009-12-31"));
						if(date.toString().endsWith("-")) {
							o.setObj6(date.toString().substring(0, matcher.group(6).length()-1));
						} else {
							o.setObj6(date.toString());
						}
							date.delete(0, date.length());
						
						if(matcher.group(7).equals("5年")){
							o.setObj7("2007-3-7");
						} else {
							date.append(matcher.group(7).trim().replace("/", "-").replace(".", "-").replace("。", "-")
									.replace("<br>", "").replace("．", "-").replace("、", "-").replace(" ", "")
									.replace("证书有效期", "").replace("有效期截止日", "").replace("200-08", "2000-08")
									.replace("20011-05", "2011-05").replace("2009-09-31", "2009-09-30")
									.replace("2009-09-32", "2009-09-30").replace("2009-09-33", "2009-09-30")
									.replace("2013-11-31", "2013-11-30"));
							if (date.toString().length() > 10) {
								o.setObj7(date.toString().substring(0, date.length()-1));
							} else if (!date.toString().equals("")
								&& Integer.valueOf(date.toString().substring(date.length()-2, date.length())) > 31) {
								o.setObj7(date.toString().substring(0,date.length()-2) + "31");
							} else {
								o.setObj7(date.toString());
							}
						}
							date.delete(0, date.length());
						
						o.setObj8(matcher.group(8));
						list.add(o);
						LOG.info(i + " 文件");
					}
				}
					writeExl(list);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		public static void main(String[] args) {
			GSP instance = new GSP();
			instance.excute();
		}
	}
